package com.yss.reportworld.service.impl;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.yss.common.core.constant.RequestDataHelper;
import com.yss.common.core.enums.YssStatusEnum;
import com.yss.common.core.exception.BusinessException;
import com.yss.common.core.utils.SpringUtils;
import com.yss.common.core.utils.StringUtils;
import com.yss.common.core.utils.easyexecl.EasyExeclUtil;
import com.yss.common.core.utils.easyexecl.ExcelListener;
import com.yss.common.core.utils.sql.SqlUtil;
import com.yss.common.core.utils.uuid.IdUtils;
import com.yss.reportworld.domain.TpGlExhibDatadict;
import com.yss.reportworld.domain.arrwrap.*;
import com.yss.reportworld.enums.ReportWorldFileNameEnum;
import com.yss.reportworld.mapper.TpGlExhibDatadictMapper;
import com.yss.reportworld.mapper.arrwrap.*;
import com.yss.reportworld.service.IArrWrapService;
import com.yss.reportworld.service.IDataCustomerService;
import com.yss.reportworld.service.ITcRepParaService;
import com.yss.reportworld.util.TableInfoUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.io.IOUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.FileCopyUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.sql.rowset.serial.SerialBlob;
import java.io.*;
import java.lang.reflect.Field;
import java.sql.Blob;
import java.sql.SQLException;
import java.util.*;
import java.util.stream.Collectors;
import java.util.zip.ZipEntry;
import java.util.zip.ZipFile;
import java.util.zip.ZipInputStream;
import java.util.zip.ZipOutputStream;


/**
 * 报表部署包接口
 *
 * @author yss
 */
@Slf4j
@Service
public class ArrWrapServiceImpl implements IArrWrapService {
    @Autowired
    private TpRepBasicSubmitMapper tpRepBasicSubmitMapper;
    @Autowired
    private TpRepBasicInfoMapper tpRepBasicInfoMapper;
    @Autowired
    private TpRepBasicStyleMapper tpRepBasicStyleMapper;
    @Autowired
    private TpRepBasicReportModelMapper tpRepBasicReportModelMapper;
    @Autowired
    private TpRepBasicMicroVariableMapper tpRepBasicMicroVariableMapper;
    @Autowired
    private TcRepParaMapper tcRepParaMapper;
    @Autowired
    private TpRepBasicColModelMapper tpRepBasicColModelMapper;
    @Autowired
    private TcRepDatasetMapper tcRepDatasetMapper;
    @Autowired
    private TpRepBasicParamMapper tpRepBasicParamMapper;
    @Autowired
    private TpRepBasicExceltemplateMapper tpRepBasicExceltemplateMapper;
    @Autowired
    private TpGlExhibDatadictMapper tpGlExhibDatadictMapper;
    @Autowired
    private ITcRepParaService tcRepParaService;
    @Autowired
    private IDataCustomerService dataCustomerService;

    // 导出cvs文件中 逗号替换空格
    private static String COMMA = "\u001F";
    private static String COMMA2 = "\u001E";
    private static String LINE_N_FEED = "\r\n";

    public byte[] downArrWrapsZip(TcRepPara tcRepPara) {
        try {
            // 处理不同版本的表结构
            TableInfoUtil.handleTableVersion(tcRepPara.getHisVersion());
            // 获取需要导出的数据
            List<String> ids = getIds(tcRepPara);
            Map<String, List> mapFile = new HashMap<>();
            QueryWrapper<TcRepPara> tcRepParaQueryWrapper = new QueryWrapper<>();
            tcRepParaQueryWrapper.in("ID", ids);
            tcRepParaQueryWrapper.eq("RW_DB_STATUS", YssStatusEnum.NORMAL.getCode());
            List<TcRepPara> tcRepParaList = tcRepParaMapper.selectList(tcRepParaQueryWrapper);
            tcRepParaList = dataCustomerService.handleBusinessPrimaryCustomer(tcRepParaList, tcRepPara.getCustomerId(), TcRepPara.class);
            if (StringUtils.isEmpty(tcRepParaList)) {
                throw new BusinessException("没有获取到你要导出的报表部署包的基本信息，请检查！");
            }
            for (TcRepPara para : tcRepParaList) {
                para.setReportName(para.getReportName().replaceAll(",", COMMA));
            }
            mapFile.put(ReportWorldFileNameEnum.TC_REP_PARA.getCode(), tcRepParaList);
            // tcRepPara里得 ReportTablename有拼接逗号得
            // 获取对应的 report_name
            List<String> listReportTablename = this.getListReportTablename(tcRepParaList);
            // 报表编码，查询结果集的时候使用
            Set<String> listReportId = tcRepParaList.stream()
                    .map(e -> new String(e.getReportId()))
                    .collect(Collectors.toSet());
            if (StringUtils.isEmpty(listReportId)) {
                throw new BusinessException("报表基本信息表中没有获取导对应的报表编号，请检查！");
            }
            // 先通过 基本信息 report_tablename 匹配取出 TP_REP_BASIC_INFO
            //报表基础信息
            QueryWrapper<TpRepBasicInfo> tpRepBasicInfoQueryWrapper = new QueryWrapper<>();
            tpRepBasicInfoQueryWrapper.in("REPORT_OVER_NAME", listReportTablename);
            // tpRepBasicInfoQueryWrapper.in("REPORT_NAME", listReportId);
            tpRepBasicInfoQueryWrapper.eq("RW_DB_STATUS", YssStatusEnum.NORMAL.getCode());
            List<TpRepBasicInfo> tpRepBasicInfoList = tpRepBasicInfoMapper.selectList(tpRepBasicInfoQueryWrapper);
            tpRepBasicInfoList = dataCustomerService.handleBusinessPrimaryCustomer(tpRepBasicInfoList, tcRepPara.getCustomerId(), TpRepBasicInfo.class);
            if (StringUtils.isEmpty(tpRepBasicInfoList)) {
                throw new BusinessException("没有获取到你要导出的报表部署包的基础信息，请检查！");
            }
            for (TpRepBasicInfo basicInfo : tpRepBasicInfoList) {
                if (StringUtils.isNull(basicInfo) || StringUtils.isEmpty(basicInfo.getReportFrag())) {
                    continue;
                }
                basicInfo.setReportFrag(basicInfo.getReportFrag().replaceAll(",", COMMA));
            }
            mapFile.put(ReportWorldFileNameEnum.TP_REP_BASIC_INFO.getCode(), tpRepBasicInfoList);
            // 报表基础信息的编号
            Set<String> listBascInfoId = tpRepBasicInfoList.stream()
                    .map(e -> new String(e.getId()))
                    .collect(Collectors.toSet());
            QueryWrapper<TpRepBasicSubmit> tpRepBasicSubmitQueryWrapper = new QueryWrapper<>();
            tpRepBasicSubmitQueryWrapper.in("REPORT_OVER_NAME", listReportTablename);
            tpRepBasicSubmitQueryWrapper.orderByAsc("REPORT_OVER_NAME");
            tpRepBasicSubmitQueryWrapper.eq("RW_DB_STATUS", YssStatusEnum.NORMAL.getCode());
            List<TpRepBasicSubmit> tpRepBasicSubmitList = tpRepBasicSubmitMapper.selectList(tpRepBasicSubmitQueryWrapper);
            tpRepBasicSubmitList = dataCustomerService.handleBusinessPrimaryCustomer(tpRepBasicSubmitList, tcRepPara.getCustomerId(), TpRepBasicSubmit.class);
            if (StringUtils.isNotEmpty(tpRepBasicSubmitList)) {
                for (TpRepBasicSubmit tpRepBasicSubmit : tpRepBasicSubmitList) {
                    if (StringUtils.isNull(tpRepBasicSubmit)) {
                        continue;
                    }
                    tpRepBasicSubmit.setSelectColumn(tpRepBasicSubmit.getSelectColumn().replaceAll(",", COMMA));
                    tpRepBasicSubmit.setSelectCondition(tpRepBasicSubmit.getSelectCondition().replaceAll(",", COMMA));
                }
            }
            mapFile.put(ReportWorldFileNameEnum.TP_REP_BASIC_SUBMIT.getCode(), tpRepBasicSubmitList);

            QueryWrapper<TpRepBasicStyle> tpRepBasicStyleQueryWrapper = new QueryWrapper<>();
            tpRepBasicStyleQueryWrapper.in("BASIC_INFO_ID", listBascInfoId);
            tpRepBasicStyleQueryWrapper.eq("RW_DB_STATUS", YssStatusEnum.NORMAL.getCode());
            List<TpRepBasicStyle> tpRepBasicStyleList = tpRepBasicStyleMapper.selectList(tpRepBasicStyleQueryWrapper);
            tpRepBasicStyleList = dataCustomerService.handleBusinessPrimaryCustomer(tpRepBasicStyleList, tcRepPara.getCustomerId(), TpRepBasicStyle.class);
            if (StringUtils.isNotEmpty(tpRepBasicStyleList)) {
                for (TpRepBasicStyle tpRepBasicStyle : tpRepBasicStyleList) {
                    if (StringUtils.isNull(tpRepBasicStyle)) {
                        continue;
                    }
                    tpRepBasicStyle.setColWidths(tpRepBasicStyle.getColWidths().replaceAll(",", COMMA));
                    tpRepBasicStyle.setRowHeights(tpRepBasicStyle.getRowHeights().replaceAll(",", COMMA));
                    tpRepBasicStyle.setRowHeights(tpRepBasicStyle.getRowHeights().replaceAll(",", COMMA));
                    tpRepBasicStyle.setMergeCellData(tpRepBasicStyle.getMergeCellData().replaceAll(",", COMMA));
                    tpRepBasicStyle.setCellStyleData(tpRepBasicStyle.getCellStyleData().replaceAll(",", COMMA));
                }
            }
            mapFile.put(ReportWorldFileNameEnum.TP_REP_BASIC_STYLE.getCode(), tpRepBasicStyleList);

            QueryWrapper<TpRepBasicReportModel> tpRepBasicReportModelQueryWrapper = new QueryWrapper<>();
            tpRepBasicReportModelQueryWrapper.in("BASIC_INFO_ID", listBascInfoId);
            tpRepBasicReportModelQueryWrapper.eq("RW_DB_STATUS", YssStatusEnum.NORMAL.getCode());
            List<TpRepBasicReportModel> tpRepBasicReportModelList = tpRepBasicReportModelMapper.selectList(tpRepBasicReportModelQueryWrapper);
            tpRepBasicReportModelList = dataCustomerService.handleBusinessPrimaryCustomer(tpRepBasicReportModelList, tcRepPara.getCustomerId(), TpRepBasicReportModel.class);
            if (StringUtils.isNotEmpty(tpRepBasicReportModelList)) {
                for (TpRepBasicReportModel tpRepBasicReportModel : tpRepBasicReportModelList) {
                    if (StringUtils.isNull(tpRepBasicReportModel)) {
                        continue;
                    }
                    tpRepBasicReportModel.setReportTitle(tpRepBasicReportModel.getReportTitle().replaceAll(",", COMMA));
                    tpRepBasicReportModel.setReportParams(tpRepBasicReportModel.getReportParams().replaceAll(",", COMMA));
                    tpRepBasicReportModel.setReportVHeader(tpRepBasicReportModel.getReportVHeader().replaceAll(",", COMMA));
                    tpRepBasicReportModel.setReportHHeader(tpRepBasicReportModel.getReportHHeader().replaceAll(",", COMMA));
                    tpRepBasicReportModel.setReportFooter(tpRepBasicReportModel.getReportFooter().replaceAll(",", COMMA));
                    tpRepBasicReportModel.setReportBody(tpRepBasicReportModel.getReportBody().replaceAll(",", COMMA));
                }
            }
            mapFile.put(ReportWorldFileNameEnum.TP_REP_BASIC_REPORT_MODEL.getCode(), tpRepBasicReportModelList);

            QueryWrapper<TpRepBasicMicroVariable> tpRepBasicMicroVariableQueryWrapper = new QueryWrapper<>();
            tpRepBasicMicroVariableQueryWrapper.in("BASIC_INFO_ID", listBascInfoId);
            tpRepBasicMicroVariableQueryWrapper.eq("RW_DB_STATUS", YssStatusEnum.NORMAL.getCode());
            List<TpRepBasicMicroVariable> tpRepBasicMicroVariableList = tpRepBasicMicroVariableMapper.selectList(tpRepBasicMicroVariableQueryWrapper);
            tpRepBasicMicroVariableList = dataCustomerService.handleBusinessPrimaryCustomer(tpRepBasicMicroVariableList, tcRepPara.getCustomerId(), TpRepBasicMicroVariable.class);
            if (StringUtils.isNotEmpty(tpRepBasicMicroVariableList)) {
                for (TpRepBasicMicroVariable tpRepBasicMicroVariable : tpRepBasicMicroVariableList) {
                    if (StringUtils.isNull(tpRepBasicMicroVariable) || StringUtils.isEmpty(tpRepBasicMicroVariable.getDataSource())) {
                        continue;
                    }
                    tpRepBasicMicroVariable.setDataSource(tpRepBasicMicroVariable.getDataSource().replaceAll(",", COMMA));
                }
            }
            mapFile.put(ReportWorldFileNameEnum.TP_REP_BASIC_MICRO_VARIABLE.getCode(), tpRepBasicMicroVariableList);
            QueryWrapper<TpRepBasicColModel> tpRepBasicColModelQueryWrapper = new QueryWrapper<>();
            tpRepBasicColModelQueryWrapper.in("BASIC_INFO_ID", listBascInfoId);
            tpRepBasicColModelQueryWrapper.eq("RW_DB_STATUS", YssStatusEnum.NORMAL.getCode());
            List<TpRepBasicColModel> tpRepBasicColModelList = tpRepBasicColModelMapper.selectList(tpRepBasicColModelQueryWrapper);
            tpRepBasicColModelList = dataCustomerService.handleBusinessPrimaryCustomer(tpRepBasicColModelList, tcRepPara.getCustomerId(), TpRepBasicColModel.class);
            mapFile.put(ReportWorldFileNameEnum.TP_REP_BASIC_COL_MODEL.getCode(), tpRepBasicColModelList);
            // 字典编码配置
            Set<String> listClassId = tpRepBasicColModelList.stream()
                    .map(e -> e.getDicDataIndex())
                    .collect(Collectors.toSet());
            mapFile.put(ReportWorldFileNameEnum.TP_GL_EXHIB_DATADICT.getCode(), null);
            if (StringUtils.isNotEmpty(listClassId)) {
                QueryWrapper<TpGlExhibDatadict> tpGlExhibDatadictQueryWrapper = new QueryWrapper<>();
                tpGlExhibDatadictQueryWrapper.in("CLASS_ID", listClassId);
                tpGlExhibDatadictQueryWrapper.orderByAsc("CLASS_ID");
                tpGlExhibDatadictQueryWrapper.eq("RW_DB_STATUS", YssStatusEnum.NORMAL.getCode());
                List<TpGlExhibDatadict> tpGlExhibDatadictList = tpGlExhibDatadictMapper.selectList(tpGlExhibDatadictQueryWrapper);
                tpGlExhibDatadictList = dataCustomerService.handleBusinessPrimaryCustomer(tpGlExhibDatadictList, tcRepPara.getCustomerId(), TpGlExhibDatadict.class);
                mapFile.put(ReportWorldFileNameEnum.TP_GL_EXHIB_DATADICT.getCode(), tpGlExhibDatadictList);
            }
            QueryWrapper<TcRepDataset> tcRepDatasetQueryWrapper = new QueryWrapper<>();
            tcRepDatasetQueryWrapper.in("REPORT_ID", listReportId);
            tcRepDatasetQueryWrapper.eq("RW_DB_DATABASE_TYPE", tcRepPara.getDataBaseType());
            tcRepDatasetQueryWrapper.orderByAsc("REPORT_ID");
            tcRepDatasetQueryWrapper.eq("RW_DB_STATUS", YssStatusEnum.NORMAL.getCode());
            List<TcRepDataset> tcRepDatasetList = tcRepDatasetMapper.selectList(tcRepDatasetQueryWrapper);
            tcRepDatasetList = dataCustomerService.handleBusinessPrimaryCustomer(tcRepDatasetList, tcRepPara.getCustomerId(), TcRepDataset.class);
            if (StringUtils.isNotEmpty(tcRepDatasetList)) {
                for (TcRepDataset tcRepDataset : tcRepDatasetList) {
                    if (StringUtils.isNull(tcRepDataset) || StringUtils.isEmpty(tcRepDataset.getDatasetSql())) {
                        continue;
                    }
                    tcRepDataset.setDatasetSql(tcRepDataset.getDatasetSql().replaceAll(",", COMMA));
                }
            }
            mapFile.put(ReportWorldFileNameEnum.TC_REP_DATASET.getCode(), tcRepDatasetList);
            QueryWrapper<TpRepBasicParam> tpRepBasicParamQueryWrapper = new QueryWrapper<>();
            tpRepBasicParamQueryWrapper.eq("RW_DB_STATUS", YssStatusEnum.NORMAL.getCode());
            List<TpRepBasicParam> tpRepBasicParamList = tpRepBasicParamMapper.selectList(tpRepBasicParamQueryWrapper);
            tpRepBasicParamList = dataCustomerService.handleBusinessPrimaryCustomer(tpRepBasicParamList, tcRepPara.getCustomerId(), TpRepBasicParam.class);
            if (StringUtils.isNotEmpty(tpRepBasicParamList)) {
                for (TpRepBasicParam tpRepBasicParam : tpRepBasicParamList) {
                    if (StringUtils.isNull(tpRepBasicParam) || StringUtils.isEmpty(tpRepBasicParam.getParamValue())) {
                        continue;
                    }
                    tpRepBasicParam.setParamValue(tpRepBasicParam.getParamValue().replaceAll(",", COMMA));
                }
            }
            mapFile.put(ReportWorldFileNameEnum.TP_REP_BASIC_PARAM.getCode(), tpRepBasicParamList);
            ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
            ZipOutputStream zip = new ZipOutputStream(outputStream);
            for (Map.Entry<String, List> entry : mapFile.entrySet()) {
                // 添加到zip
                zip.putNextEntry(new ZipEntry(entry.getKey() + "." + ExcelTypeEnum.CSV));
                Object obj = SpringUtils.getBean(ReportWorldFileNameEnum.getEnumDesc(entry.getKey()));
                // 表头信息，直接导出表头会出现 \uFEFF"字符在表头的最前面
                List<String> heards = new ArrayList<>();
                Field[] fields = obj.getClass().getDeclaredFields();
                for (Field field : fields) {
                    if (StringUtils.isNull(field)) {
                        continue;
                    }
                    field.setAccessible(true);
                    ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
                    if (StringUtils.isNull(excelProperty) || StringUtils.isEmpty(excelProperty.value())) {
                        field.setAccessible(false);
                        continue;
                    }
                    heards.add(excelProperty.value()[0]);
                    field.setAccessible(false);
                }
                String[] heardsArr = heards.toArray(new String[heards.size()]);
                byte[] data = EasyExeclUtil.exportExcelByte(obj, entry.getValue(), ExcelTypeEnum.CSV, heardsArr, entry.getKey());
                zip.write(data);
                zip.flush();
                zip.closeEntry();
            }
            this.handleTpRepBasicExceltemplate(zip, tpRepBasicInfoList, tcRepPara.getCustomerId());
            IOUtils.closeQuietly(zip);
            return outputStream.toByteArray();
        } catch (Exception ex) {
            log.error("下载ZIP文件失败,", ex.getMessage());
            throw new BusinessException("下载ZIP文件失败," + ex.getMessage());
        } finally {
            RequestDataHelper.removeThreadLocal();
        }
    }


    // 结果集个性化处理


    /**
     * 获取需要导出的数据
     *
     * @param tcRepPara
     */
    private List<String> getIds(TcRepPara tcRepPara) {
        List<String> ids = null;
        if (1 == tcRepPara.getExportType()) { //导出方式是监管规则
            // 获取监管规则下的所有报表编号
            List<TcRepPara> list = tcRepParaService.selectTcRepParaList(tcRepPara);
            if (StringUtils.isEmpty(list)) {
                throw new BusinessException("没有获取到你要导出的报表信息，请检查！");
            }
            ids = list.stream()
                    .map(e -> new String(e.getId()))
                    .collect(Collectors.toList());
        } else { //报表编号导出
            ids = tcRepPara.getIds();
        }
        if (StringUtils.isEmpty(ids)) {
            throw new BusinessException("没有获取到你要导出的报表信息，请检查！");
        }
        return ids;
    }

    /**
     * 获取用要的报表落地表表名信息
     *
     * @param tcRepParaList
     * @return
     */
    private List<String> getListReportTablename(List<TcRepPara> tcRepParaList) {
        List<String> listReportTablename = new ArrayList<>();
        for (TcRepPara repPara : tcRepParaList) {
            if (StringUtils.isNull(repPara) || StringUtils.isEmpty(repPara.getReportTablename())) {
                continue;
            }
            if (repPara.getReportTablename().indexOf(",") > -1) {
                String[] arr = repPara.getReportTablename().split(",");
                if (null == arr || arr.length <= 0) {
                    continue;
                }
                listReportTablename.addAll(Arrays.asList(arr));
            }
            listReportTablename.add(repPara.getReportTablename());
        }
        if (StringUtils.isEmpty(listReportTablename)) {
            throw new BusinessException("报表基本信息表中没有获取导对应的落地表名的信息，请检查！");
        }
        return listReportTablename;
    }

    // 部署包模板存储对象的数据处理成execl放了压缩包
    private void handleTpRepBasicExceltemplate(ZipOutputStream zip, List<TpRepBasicInfo> tpRepBasicInfoList, String customerId) throws Exception {
        // 查询对应的模板放入zip文件里面
        List<String> listBascInfoExcelName = tpRepBasicInfoList.stream()
                .map(e -> new String(e.getExcelName()))
                .collect(Collectors.toList());
        if (StringUtils.isEmpty(listBascInfoExcelName)) {
            return;
        }
        QueryWrapper<TpRepBasicExceltemplate> tpRepBasicExceltemplateQueryWrapper = new QueryWrapper<>();
        tpRepBasicExceltemplateQueryWrapper.in("TEMPLATE_NAME", listBascInfoExcelName);
        tpRepBasicExceltemplateQueryWrapper.eq("RW_DB_STATUS", YssStatusEnum.NORMAL.getCode());
        List<TpRepBasicExceltemplate> tpRepBasicExceltemplates = tpRepBasicExceltemplateMapper.selectList(tpRepBasicExceltemplateQueryWrapper);
        tpRepBasicExceltemplates = dataCustomerService.handleBusinessPrimaryCustomer(tpRepBasicExceltemplates, customerId, TpRepBasicExceltemplate.class);
        if (StringUtils.isEmpty(tpRepBasicExceltemplates)) {
            return;
        }
        for (TpRepBasicExceltemplate tpRepBasicExceltemplate : tpRepBasicExceltemplates) {
            if (StringUtils.isNull(tpRepBasicExceltemplate) || null == tpRepBasicExceltemplate.getTemplateFile()) {
                continue;
            }
            //String base64 = this.convertBlobToBase64(new SerialBlob(tpRepBasicExceltemplate.getTemplateFile()));
            byte[] bytes = Base64.getDecoder().decode(tpRepBasicExceltemplate.getTemplateFile());
            Blob blob = new SerialBlob(bytes);
            zip.putNextEntry(new ZipEntry(tpRepBasicExceltemplate.getTemplateName()));
            zip.write(blob.getBytes(1, (int) blob.length()));
            zip.flush();
            zip.closeEntry();
        }
    }

    /**
     * blob 转 Base64
     *
     * @param blob
     * @return
     * @throws SQLException
     * @throws IOException
     */
    public String convertBlobToBase64(Blob blob) throws SQLException, IOException {
        // 读取Blob对象中的二进制数据
        InputStream inputStream = blob.getBinaryStream();
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        byte[] buffer = new byte[4096];
        int bytesRead;
        while ((bytesRead = inputStream.read(buffer)) != -1) {
            outputStream.write(buffer, 0, bytesRead);
        }
        // 将二进制数据转换为Base64编码
        byte[] data = outputStream.toByteArray();
        String base64 = Base64.getEncoder().encodeToString(data);
        // 关闭资源
        inputStream.close();
        outputStream.close();
        return base64;
    }


    @Override
    public String getArrWrapsSql(MultipartFile multipartFile, String hisVersion) {
        ExcelReader excelReader = null;
        StringBuffer sqlBuffer = new StringBuffer();
        try {
            ZipInputStream zin = new ZipInputStream(multipartFile.getInputStream());
            // 获取文件名
            String multipartFileName = multipartFile.getOriginalFilename();
            // 获取文件后缀
            String prefix = multipartFileName.substring(multipartFileName.lastIndexOf("."));
            if (!StringUtils.oneCase(prefix.toUpperCase(), ".ZIP")) {
                throw new BusinessException("请导入zip格式文件");
            }
            // 若需要防止生成的临时文件重复,可以在文件名后添加随机码
            File fileNew = File.createTempFile(System.currentTimeMillis() + "", prefix);
            OutputStream os = new FileOutputStream(fileNew);
            FileCopyUtils.copy(multipartFile.getInputStream(), os);
            ZipFile zipFile = new ZipFile(fileNew);
            ZipEntry entry;
            List<TpRepBasicSubmit> tpRepBasicSubmitList = null;
            List<TpRepBasicStyle> tpRepBasicStyleList = null;
            List<TpRepBasicReportModel> tpRepBasicReportModelList = null;
            List<TpRepBasicParam> tpRepBasicParamList = null;
            List<TpRepBasicMicroVariable> tpRepBasicMicroVariableList = null;
            List<TpRepBasicInfo> tpRepBasicInfoList = null;
            List<TpRepBasicColModel> tpRepBasicColModeList = null;
            List<TcRepPara> tcRepParaList = null;
            List<TcRepDataset> tcRepDatasetList = null;
            while ((entry = zin.getNextEntry()) != null) {
                try (InputStream in = zipFile.getInputStream(entry)) {
                    String fileName = entry.getName();
                    if (fileName.toUpperCase().endsWith(".XLSX") ||
                            fileName.toUpperCase().endsWith(".XLS")) {
                        BufferedInputStream bufferedInputStream = new BufferedInputStream(in);
                        byte[] bytes = new byte[bufferedInputStream.available()];
                        bufferedInputStream.read(bytes);
                        Blob blob = new SerialBlob(bytes);
                        String base64 = this.convertBlobToBase64(blob);
                        sqlBuffer.append("INSERT INTO TP_REP_BASIC_EXCELTEMPLATE (ID, TEMPLATE_NAME, FILE_SIZE, SHEET_SIZE, TEMPLATE_FILE) " +
                                "VALUES('" + IdUtils.fastSimpleUUID() + "', '" + fileName + "', 0, NULL, '" + base64 + "');").append("\r\n");
                    }
                    if (fileName.toUpperCase().endsWith(".CSV")) {
                        fileName = fileName.toUpperCase().replace(".CSV", "");
                        BufferedInputStream bufferedInputStream = new BufferedInputStream(in);
                        if (ReportWorldFileNameEnum.TP_REP_BASIC_SUBMIT.getCode().equals(fileName)) {
                            excelReader = EasyExcel.read(bufferedInputStream).build();
                            ExcelListener<TpRepBasicSubmit> tpRepBasicSubmitListener = new ExcelListener<>();
                            ReadSheet tpRepBasicSubmitSheet = EasyExcel.readSheet(0).head(TpRepBasicSubmit.class).headRowNumber(1).registerReadListener(tpRepBasicSubmitListener).build();
                            excelReader.read(tpRepBasicSubmitSheet);
                            tpRepBasicSubmitList = tpRepBasicSubmitListener.getDatas();
                            continue;
                        }
                        if (ReportWorldFileNameEnum.TP_REP_BASIC_STYLE.getCode().equals(fileName)) {
                            excelReader = EasyExcel.read(bufferedInputStream).build();
                            ExcelListener<TpRepBasicStyle> tpRepBasicStyleListener = new ExcelListener<>();
                            ReadSheet tpRepBasicSttyleSheet = EasyExcel.readSheet(0).head(TpRepBasicStyle.class).headRowNumber(1).registerReadListener(tpRepBasicStyleListener).build();
                            excelReader.read(tpRepBasicSttyleSheet);
                            tpRepBasicStyleList = tpRepBasicStyleListener.getDatas();
                            continue;
                        }
                        if (ReportWorldFileNameEnum.TP_REP_BASIC_REPORT_MODEL.getCode().equals(fileName)) {
                            excelReader = EasyExcel.read(bufferedInputStream).build();
                            ExcelListener<TpRepBasicReportModel> tpRepBasicReportModelListener = new ExcelListener<>();
                            ReadSheet tpRepBasicSttyleSheet = EasyExcel.readSheet(0).head(TpRepBasicReportModel.class).headRowNumber(1).registerReadListener(tpRepBasicReportModelListener).build();
                            excelReader.read(tpRepBasicSttyleSheet);
                            tpRepBasicReportModelList = tpRepBasicReportModelListener.getDatas();
                            continue;
                        }
                        if (ReportWorldFileNameEnum.TP_REP_BASIC_PARAM.getCode().equals(fileName)) {
                            excelReader = EasyExcel.read(bufferedInputStream).build();
                            ExcelListener<TpRepBasicParam> tpRepBasicParamListener = new ExcelListener<>();
                            ReadSheet tpRepBasicSttyleSheet = EasyExcel.readSheet(0).head(TpRepBasicParam.class).headRowNumber(1).registerReadListener(tpRepBasicParamListener).build();
                            excelReader.read(tpRepBasicSttyleSheet);
                            tpRepBasicParamList = tpRepBasicParamListener.getDatas();
                            continue;
                        }
                        if (ReportWorldFileNameEnum.TP_REP_BASIC_MICRO_VARIABLE.getCode().equals(fileName)) {
                            excelReader = EasyExcel.read(bufferedInputStream).build();
                            ExcelListener<TpRepBasicMicroVariable> tpRepBasicMicroVariableListener = new ExcelListener<>();
                            ReadSheet tpRepBasicSttyleSheet = EasyExcel.readSheet(0).head(TpRepBasicMicroVariable.class).headRowNumber(1).registerReadListener(tpRepBasicMicroVariableListener).build();
                            excelReader.read(tpRepBasicSttyleSheet);
                            tpRepBasicMicroVariableList = tpRepBasicMicroVariableListener.getDatas();
                            continue;
                        }
                        if (ReportWorldFileNameEnum.TP_REP_BASIC_INFO.getCode().equals(fileName)) {
                            excelReader = EasyExcel.read(bufferedInputStream).build();
                            ExcelListener<TpRepBasicInfo> tpRepBasicInfoListener = new ExcelListener<>();
                            ReadSheet tpRepBasicSttyleSheet = EasyExcel.readSheet(0).head(TpRepBasicInfo.class).headRowNumber(1).registerReadListener(tpRepBasicInfoListener).build();
                            excelReader.read(tpRepBasicSttyleSheet);
                            tpRepBasicInfoList = tpRepBasicInfoListener.getDatas();
                            continue;
                        }
                        if (ReportWorldFileNameEnum.TP_REP_BASIC_COL_MODEL.getCode().equals(fileName)) {
                            excelReader = EasyExcel.read(bufferedInputStream).build();
                            ExcelListener<TpRepBasicColModel> tpRepBasicColModelListener = new ExcelListener<>();
                            ReadSheet tpRepBasicSttyleSheet = EasyExcel.readSheet(0).head(TpRepBasicColModel.class).headRowNumber(1).registerReadListener(tpRepBasicColModelListener).build();
                            excelReader.read(tpRepBasicSttyleSheet);
                            tpRepBasicColModeList = tpRepBasicColModelListener.getDatas();
                            continue;
                        }
                        if (ReportWorldFileNameEnum.TP_GL_EXHIB_DATADICT.getCode().equals(fileName)) {
                            continue;
                        }
                        if (ReportWorldFileNameEnum.TC_REP_PARA.getCode().equals(fileName)) {
                            excelReader = EasyExcel.read(bufferedInputStream).build();
                            ExcelListener<TcRepPara> tcRepParaListener = new ExcelListener<>();
                            ReadSheet tcRepParaSheet = EasyExcel.readSheet(0).head(TcRepPara.class).headRowNumber(1).registerReadListener(tcRepParaListener).build();
                            excelReader.read(tcRepParaSheet);
                            tcRepParaList = tcRepParaListener.getDatas();
                            continue;
                        }
                        if (ReportWorldFileNameEnum.TC_REP_DATASET.getCode().equals(fileName)) {
                            excelReader = EasyExcel.read(bufferedInputStream).build();
                            ExcelListener<TcRepDataset> tcRepDatasetListener = new ExcelListener<>();
                            ReadSheet tcRepDatasetSheet = EasyExcel.readSheet(0).head(TcRepDataset.class).headRowNumber(1).registerReadListener(tcRepDatasetListener).build();
                            excelReader.read(tcRepDatasetSheet);
                            tcRepDatasetList = tcRepDatasetListener.getDatas();
                            continue;
                        }
                    }
                }
            }
            // 现在生成基础信息
            TableInfoUtil.handleTableVersion(hisVersion); // 替换表名
            sqlBuffer.append(this.getRpRepBasicInfoSql(tpRepBasicInfoList, tpRepBasicSubmitList, tpRepBasicStyleList,
                    tpRepBasicReportModelList, tpRepBasicParamList, tpRepBasicMicroVariableList,
                    tpRepBasicColModeList, tcRepParaList, tcRepDatasetList));
        } catch (Exception ex) {
            log.error(ex.getMessage());
            throw new BusinessException("生成报表部署包的SQL语句出错，" + ex.getMessage());
        } finally {
            RequestDataHelper.removeThreadLocal();
        }
        return sqlBuffer.toString();
    }

    /**
     * 文件的数据信息生成SQL语句
     *
     * @param tpRepBasicInfoList
     * @param tpRepBasicSubmitList
     * @param tpRepBasicStyleList
     * @param tpRepBasicReportModelList
     * @param tpRepBasicParamList
     * @param tpRepBasicMicroVariableList
     * @param tpRepBasicColModeList
     * @param tcRepParaList
     * @param tcRepDatasetList
     * @return
     */
    private String getRpRepBasicInfoSql(List<TpRepBasicInfo> tpRepBasicInfoList, List<TpRepBasicSubmit> tpRepBasicSubmitList,
                                        List<TpRepBasicStyle> tpRepBasicStyleList, List<TpRepBasicReportModel> tpRepBasicReportModelList,
                                        List<TpRepBasicParam> tpRepBasicParamList, List<TpRepBasicMicroVariable> tpRepBasicMicroVariableList,
                                        List<TpRepBasicColModel> tpRepBasicColModeList, List<TcRepPara> tcRepParaList, List<TcRepDataset> tcRepDatasetList) {
        if (StringUtils.isEmpty(tpRepBasicInfoList)) {
            return "";
        }
        // 基础信息如果标准库存在就替换成标准库，不存在就用文件的
        Map<String, String> tpRepBasicInfoMap = new HashMap<>();
        StringBuffer sqlBuffer = new StringBuffer();
        // 标准库里所有的基础信息
        Map<String, String> tpRepBasicInfoStaMap = tpRepBasicInfoMapper.selectList(new QueryWrapper<>()).
                stream().collect(Collectors.toMap(e -> e.getReportOverName(), e -> e.getId()));
        // 文件里所有基础信息
        for (TpRepBasicInfo tpRepBasicInfo : tpRepBasicInfoList) {
            // 标准库已经存在，就替换成成标准库的id
            if (tpRepBasicInfoStaMap.containsKey(tpRepBasicInfo.getReportOverName())) {
                // 文件里的id要替换成标准库的id
                tpRepBasicInfoMap.put(tpRepBasicInfo.getId(), tpRepBasicInfoStaMap.get(tpRepBasicInfo.getReportOverName()));
                tpRepBasicInfo.setId(tpRepBasicInfoStaMap.get(tpRepBasicInfo.getReportOverName()));
            } else {
                String id = IdUtils.fastSimpleUUID();
                tpRepBasicInfo.setId(id);
                tpRepBasicInfoMap.put(id, tpRepBasicInfo.getReportOverName());
            }
            tpRepBasicInfo.setReportFrag(tpRepBasicInfo.getReportFrag().replaceAll(COMMA, ",").replaceAll(COMMA2, LINE_N_FEED));
        }
        sqlBuffer.append(SqlUtil.getSqlByList(ReportWorldFileNameEnum.TP_REP_BASIC_INFO.getCode(), tpRepBasicInfoList, TpRepBasicInfo.class));
        if (StringUtils.isNotEmpty(tpRepBasicSubmitList)) {
            for (TpRepBasicSubmit submit : tpRepBasicSubmitList) {
                submit.setId(IdUtils.fastSimpleUUID());
                submit.setSelectColumn(submit.getSelectColumn().replaceAll(COMMA, ",").replaceAll(COMMA2, LINE_N_FEED));
                submit.setSelectCondition(submit.getSelectCondition().replaceAll(COMMA, ",").replaceAll(COMMA2, LINE_N_FEED));
            }
            //都是FISP002的不用读取
            // sqlBuffer.append(SqlUtil.getSqlByList(ReportWorldFileNameEnum.TP_REP_BASIC_SUBMIT.getCode(), tpRepBasicSubmitList, TpRepBasicSubmit.class));
        }
        if (StringUtils.isNotEmpty(tpRepBasicStyleList)) {
            for (TpRepBasicStyle tpRepBasicStyle : tpRepBasicStyleList) {
                tpRepBasicStyle.setId(IdUtils.fastSimpleUUID());
                if (tpRepBasicInfoMap.containsKey(tpRepBasicStyle.getBasicInfoId())) {
                    tpRepBasicStyle.setBasicInfoId(tpRepBasicInfoMap.get(tpRepBasicStyle.getBasicInfoId()));
                    tpRepBasicStyle.setColWidths(tpRepBasicStyle.getColWidths().replaceAll(COMMA, ",").replaceAll(COMMA2, LINE_N_FEED));
                    tpRepBasicStyle.setRowHeights(tpRepBasicStyle.getRowHeights().replaceAll(COMMA, ",").replaceAll(COMMA2, LINE_N_FEED));
                    tpRepBasicStyle.setRowHeights(tpRepBasicStyle.getRowHeights().replaceAll(COMMA, ",").replaceAll(COMMA2, LINE_N_FEED));
                    tpRepBasicStyle.setMergeCellData(tpRepBasicStyle.getMergeCellData().replaceAll(COMMA, ",").replaceAll(COMMA2, LINE_N_FEED));
                    tpRepBasicStyle.setCellStyleData(tpRepBasicStyle.getCellStyleData().replaceAll(COMMA, ",").replaceAll(COMMA2, LINE_N_FEED));
                }
            }
            sqlBuffer.append(SqlUtil.getSqlByList(ReportWorldFileNameEnum.TP_REP_BASIC_STYLE.getCode(), tpRepBasicStyleList, TpRepBasicStyle.class));
        }
        if (StringUtils.isNotEmpty(tpRepBasicReportModelList)) {
            for (TpRepBasicReportModel model : tpRepBasicReportModelList) {
                model.setId(IdUtils.fastSimpleUUID());
                if (tpRepBasicInfoMap.containsKey(model.getBasicInfoId())) {
                    model.setBasicInfoId(tpRepBasicInfoMap.get(model.getBasicInfoId()));
                    model.setReportTitle(model.getReportTitle().replaceAll(COMMA, ",").replaceAll(COMMA2, LINE_N_FEED));
                    model.setReportParams(model.getReportParams().replaceAll(COMMA, ",").replaceAll(COMMA2, LINE_N_FEED));
                    model.setReportVHeader(model.getReportVHeader().replaceAll(COMMA, ",").replaceAll(COMMA2, LINE_N_FEED));
                    model.setReportHHeader(model.getReportHHeader().replaceAll(COMMA, ",").replaceAll(COMMA2, LINE_N_FEED));
                    model.setReportFooter(model.getReportFooter().replaceAll(COMMA, ",").replaceAll(COMMA2, LINE_N_FEED));
                    model.setReportBody(model.getReportBody().replaceAll(COMMA, ",").replaceAll(COMMA2, LINE_N_FEED));
                }
            }
            sqlBuffer.append(SqlUtil.getSqlByList(ReportWorldFileNameEnum.TP_REP_BASIC_REPORT_MODEL.getCode(), tpRepBasicReportModelList, TpRepBasicReportModel.class));
        }
        if (StringUtils.isNotEmpty(tpRepBasicParamList)) {
            for (TpRepBasicParam param : tpRepBasicParamList) {
                param.setId(IdUtils.fastSimpleUUID());
                param.setParamValue(param.getParamValue()
                        .replaceAll(COMMA, ",")
                        .replaceAll(COMMA2, LINE_N_FEED));
            }
            // 每套报表都有的
            //sqlBuffer.append(SqlUtil.getSqlByList(ReportWorldFileNameEnum.TP_REP_BASIC_PARAM.getCode(), tpRepBasicParamList, TpRepBasicParam.class));
        }
        if (StringUtils.isNotEmpty(tpRepBasicMicroVariableList)) {
            for (TpRepBasicMicroVariable microVariable : tpRepBasicMicroVariableList) {
                microVariable.setId(IdUtils.fastSimpleUUID());
                if (tpRepBasicInfoMap.containsKey(microVariable.getBasicInfoId())) {
                    microVariable.setBasicInfoId(tpRepBasicInfoMap.get(microVariable.getBasicInfoId()));
                    microVariable.setDataSource(microVariable.getDataSource()
                            .replaceAll(COMMA, ",")
                            .replaceAll(COMMA2, LINE_N_FEED));
                }
            }
            sqlBuffer.append(SqlUtil.getSqlByList(ReportWorldFileNameEnum.TP_REP_BASIC_MICRO_VARIABLE.getCode(), tpRepBasicMicroVariableList, TpRepBasicMicroVariable.class));
        }
        if (StringUtils.isNotEmpty(tpRepBasicColModeList)) {
            for (TpRepBasicColModel colModel : tpRepBasicColModeList) {
                colModel.setId(IdUtils.fastSimpleUUID());
                if (tpRepBasicInfoMap.containsKey(colModel.getBasicInfoId())) {
                    colModel.setBasicInfoId(tpRepBasicInfoMap.get(colModel.getBasicInfoId()));
                }
            }
            sqlBuffer.append(SqlUtil.getSqlByList(ReportWorldFileNameEnum.TP_REP_BASIC_COL_MODEL.getCode(), tpRepBasicColModeList, TpRepBasicColModel.class));
        }
        if (StringUtils.isNotEmpty(tcRepParaList)) {
            for (TcRepPara tcRepPara : tcRepParaList) {
                tcRepPara.setId(IdUtils.fastSimpleUUID());
                tcRepPara.setReportTablename(tcRepPara.getReportTablename()
                        .replaceAll(COMMA, ",")
                        .replaceAll(COMMA2, LINE_N_FEED));
            }
            sqlBuffer.append(SqlUtil.getSqlByList(ReportWorldFileNameEnum.TC_REP_PARA.getCode(), tcRepParaList, TcRepPara.class));

        }
        if (StringUtils.isNotEmpty(tcRepDatasetList)) {
            for (TcRepDataset dataset : tcRepDatasetList) {
                dataset.setId(IdUtils.fastSimpleUUID());
                dataset.setDatasetSql(dataset.getDatasetSql()
                        .replaceAll(COMMA, ",")
                        .replaceAll(COMMA2, LINE_N_FEED));
            }
            sqlBuffer.append(SqlUtil.getSqlByList(ReportWorldFileNameEnum.TC_REP_DATASET.getCode(), tcRepDatasetList, TcRepDataset.class));

        }

        return sqlBuffer.toString();
    }

}
